package book;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import member.MemberBean;

import common.DBManager;

public class ReturnEntryServlet extends HttpServlet {

	static Connection connection=null;
	static Statement statement=null;
	static ResultSet resultSet=null;


	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		resp.setCharacterEncoding("UTF-8");
		resp.setContentType("UTF-8");

		sendInfo(req, resp);
		rbookInfo(req,resp);
	}


	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		resp.setCharacterEncoding("UTF-8");
		resp.setContentType("UTF-8");

		sendInfo(req, resp);
		rbookInfo(req,resp);
	}



	public void sendInfo(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		// ページ設定
		String pageStr = req.getParameter("page");
		int page;
		int max = 10;	// 1ページあたりの表示件数
		if(pageStr == null) {
			page = 1;
		} else {
			page = Integer.parseInt(pageStr);
		}
		int start = (page - 1) * max;

//---利用者情報の取得-----------------------------------------------------------------

		final String PATH_P141 =  "/member/RentalListServlet";
		final String PATH_P251 =  "/member/userBookReturn.jsp";

		String path 		= null;																			//	遷移先アドレス
		String message 	= null;																			//	メッセージ

		String strId = req.getParameter("id");
		int user_id = Integer.parseInt(strId);

		if (strId == null) {
			path = PATH_P141;
			message = "返却者を選択してください。";
		}

//		利用者テーブルより、ユーザ管理ＩＤが一致するレコードを取得する。
		MemberBean member = getMemberInfomation(user_id);
		//				利用者レコードが存在しない場合
		//						メッセージに、「利用者を選択してください。」をセットし、利用者検索結果に遷移する。
		if (member == null) {
			path = PATH_P141;
			message = "返却者を選択してください。";
		}
		//				利用者レコードが存在する場合
		//						利用者レコードより、各項目をリクエスト情報にセットし、利用者削除確認（Ｐ１３２）へ遷移する。
		else {
			path = PATH_P251;
			req.setAttribute("member", member);
		}

		req.setAttribute("msg", message);

//		RequestDispatcher requestDispatcher=req.getRequestDispatcher(path);
//		requestDispatcher.forward(req, resp);
	}

	public static MemberBean getMemberInfomation(int user_id)
	{
		connection=DBManager.getConnection();
		MemberBean memberBean =null;

		try
		{
			statement=connection.createStatement();
			resultSet=statement.executeQuery("SELECT * FROM members WHERE ID = "+ user_id);

			while(resultSet.next())
			{
				memberBean=new MemberBean();

				memberBean.setId(resultSet.getInt("ID"));
				memberBean.setUserId(resultSet.getString("USER_ID"));
				memberBean.setName(resultSet.getString("NAME"));
				memberBean.setRuby(resultSet.getString("RUBY"));
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch(NumberFormatException e)
		{
			e.printStackTrace();
		}
		finally
		{
			if(statement!=null)
			{
				try
				{
					statement.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}

			if(connection!=null)
			{
				try
				{
					connection.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}

		return memberBean;
	}

	public void rbookInfo(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {


		final String PATH_P241 = "/book/RentalEntryServlet";
		final String PATH_P251 = "/member/userBookReturn.jsp";

//---貸出日の取得---------------------------------------------------------------------

		String user_id = req.getParameter("id");
	//	int rental_date = Integer.parseInt(rentaldate);

	//	String title = req.getParameter("title");
	//	String isbn = req.getParameter("isbn");

		List<RentalBean> list = getRentalBook(user_id);

		String path = null;
		path = PATH_P251;
		req.setAttribute("rentals", list);

		RequestDispatcher requestDispatcher=req.getRequestDispatcher(path);
		requestDispatcher.forward(req, resp);
	}

	private List<RentalBean> getRentalBook(String user_id) {

		connection = DBManager.getConnection();
	//	RentalBean rentalBean = null;
		List<RentalBean> list = new ArrayList<RentalBean>();

		try{
			statement=connection.createStatement();

		//	返却済みの書籍を表示しないように変更	2013.10.18	若林
		//	resultSet=statement.executeQuery("SELECT * FROM rentals WHERE MEMBER_ID="+ user_id + " ORDER BY RENTAL_DATE ");
			resultSet=statement.executeQuery("SELECT * FROM rentals WHERE RETURN_DATE IS NULL AND MEMBER_ID = "+ user_id + " ORDER BY RENTAL_DATE ");

			String sql_book = "select * from books where id = ?";
			Connection con = DBManager.getConnection();
			PreparedStatement sql = con.prepareStatement(sql_book);

			while(resultSet.next())
			{

				RentalBean rentalBean=new RentalBean();

				rentalBean.setRentalDate(resultSet.getDate("RENTAL_DATE"));

				sql.setInt(1, resultSet.getInt("book_id"));
				ResultSet rs = sql.executeQuery();

				if (rs.next()) {
					rentalBean.setBookId(resultSet.getInt("book_id"));
					rentalBean.setTitle(rs.getString("TITLE"));
					rentalBean.setIsbn(rs.getString("ISBN"));
					//	貸出情報のIDに利用者管理IDをセットしているため修正。	2013.10.18	若林
					//	rentalBean.setId(Integer.parseInt(user_id));
					rentalBean.setId(resultSet.getInt("ID"));
				}

				//	一覧データに追加
				list.add(rentalBean);
			}
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		catch(NumberFormatException e){
			e.printStackTrace();
		}
		finally{
			if(statement!=null){
				try{
						statement.close();
				}
				catch (SQLException e){
						e.printStackTrace();
				}
			}

			if(connection!=null){
				try{
						connection.close();
				}
				catch (SQLException e){
						e.printStackTrace();
				}
			}
		}
		return list;
	}
}
